<?php

if (file_exists(APPPATH.'models/basemodel.php')) include_once(APPPATH.'models/basemodel.php');

class TimesheetModel extends BaseModel {

	public function TimesheetModel() {
		parent::BaseModel();
		$this->setModel('timesheet');
		$this->obj =& get_instance();
		$this->load->database();
	}
	
	public function delete_by_pkey($idField){
		$sql = "DELETE T, TD
				FROM timesheet T LEFT JOIN timesheet_dismissed TD ON (T.id = TD.timesheetid)
				WHERE T.id = ".intval($idField);

		$query = $this->db->query($sql);

		return true;
	}
	
	public function getTimeSpent($jobid, $job_costcenterid = false, $notbillable = 0, $userid = false) {
		$sql = "SELECT SUM(hoursworked) as totalhours
				FROM timesheet T
				WHERE jobid = ".intval($jobid);
				
		if ($job_costcenterid !== false) $sql .= " AND job_costcenterid = ".intval($job_costcenterid)." ";
		
		$sql .= " AND notbillable = ".intval($notbillable);
		
		if ($userid !== false) $sql .= " AND creatorid = ".intval($userid)." ";
	
		$rs = $this->db->query($sql)->result();
		
		if (count($rs)) return round($rs[0]->totalhours, 1);
		else return false;
	}
	
	public function getTimeSpentByDay($jobid, $job_costcenterid = false, $notbillable = 0, $userid = false) {
		$sql = "SELECT T.dateapplicable, SUM(hoursworked) as totalhours
				FROM timesheet T
				WHERE jobid = ".intval($jobid)." AND T.hoursworked > 0";
				
		if ($job_costcenterid !== false) $sql .= " AND job_costcenterid = ".intval($job_costcenterid)." ";
		
		$sql .= " AND notbillable = ".intval($notbillable);
		
		if ($userid !== false) $sql .= " AND creatorid = ".intval($userid)." ";
		
		$sql .= " GROUP BY year(dateapplicable), month(dateapplicable), day(dateapplicable) ORDER BY T.dateapplicable ASC ";
	
		$rs = $this->db->query($sql)->result();
		
		if (count($rs)) return $rs;
		else return false;
	}
	
	public function getUpdatesFor($companyid, $jobid = 0, $costcenterid = 0, $activityid = 0) {
		$sql = "SELECT T.*, CO.company_name, A.activity_title, C.centertitle, J.job_title, U.firstname, U.lastname
				FROM timesheet T LEFT JOIN activity A ON (T.activityid = A.id)
					LEFT JOIN job J ON (T.jobid = J.id)
					LEFT JOIN job_costcenter JC ON (T.job_costcenterid = JC.id)
					LEFT JOIN costcenter C ON (JC.costcenterid = C.id)
					LEFT JOIN users U ON (T.creatorid = U.id)
					LEFT JOIN company CO ON (T.companyid = CO.id)
				WHERE T.companyid = ".intval($companyid);
	
		if ($jobid) $sql .= " AND T.jobid = ".intval($jobid);
		if ($costcenterid) $sql .= " AND T.job_costcenterid = ".intval($costcenterid);
		if ($activityid) $sql .= " AND T.activityid = ".intval($activityid);
		
		$sql .=	" ORDER BY T.dateapplicable DESC";

		$rs = $this->db->query($sql)->result();
		
		if (count($rs)) return $rs;
		else return false;
		
	}
	
	public function whatsGoingOn($jobid = false, $usersid = false, $from = false, $to = false, $assignedonly = false, $companyid = false, $publiconly = false) {
		/*$sql = "SELECT T.*, CO.company_name, A.activity_title, C.centertitle, J.job_title, U.firstname, U.lastname, U.emailaddress, TD.usersid as dismissed
				FROM timesheet T LEFT JOIN activity A ON (T.activityid = A.id)
					LEFT JOIN job J ON (T.jobid = J.id)
					LEFT JOIN job_costcenter JC ON (T.job_costcenterid = JC.id)
					LEFT JOIN costcenter C ON (JC.costcenterid = C.id)
					LEFT JOIN users U ON (T.creatorid = U.id)
					LEFT JOIN company CO ON (T.companyid = CO.id)
					LEFT JOIN timesheet_dismissed TD ON (T.id = TD.timesheetid AND TD.usersid = ".intval($usersid).")
				WHERE 1 = 1 ";*/
				
		$sql = "SELECT DISTINCT T.*, CO.company_name, A.activity_title, C.centertitle, J.job_title, U.firstname, U.lastname, U.emailaddress, TD.usersid as dismissed 
				FROM timesheet T ";
				
		if ($assignedonly) $sql .= " INNER JOIN assignment S ON ( (T.jobid = S.jobid AND T.jobid > 0) OR (S.job_costcenterid = T.job_costcenterid AND T. job_costcenterid > 0) ) ";
		
		$sql .=	" LEFT JOIN activity A ON (T.activityid = A.id)
					LEFT JOIN job J ON (T.jobid = J.id)
					LEFT JOIN job_costcenter JC ON (T.job_costcenterid = JC.id)
					LEFT JOIN costcenter C ON (JC.costcenterid = C.id)
					LEFT JOIN users U ON (T.creatorid = U.id)
					LEFT JOIN company CO ON (T.companyid = CO.id)
					LEFT JOIN timesheet_dismissed TD ON (T.id = TD.timesheetid AND TD.usersid = ".intval($usersid).")
				WHERE 1 = 1 ";
		
		if ($publiconly) $sql .= " AND T.public = 1 ";
		
		if ($assignedonly) $sql .= " AND S.userid = ".intval($usersid)." ";
	
		if ($companyid) {
			if (is_array($companyid)) $sql .= " AND T.companyid IN (".implode(',', $companyid).")";
			else $sql .= " AND T.companyid = ".intval($companyid);
		}
		
		if ($jobid) {
			if (is_array($jobid)) $sql .= " AND T.jobid IN (".implode(',', $jobid).")";
			else $sql .= " AND T.jobid = ".intval($jobid);
		}

		if ($from) $sql .= " AND T.dateapplicable >= ".$this->db->escape($from);
		if ($to) $sql .= " AND T.dateapplicable <= ".$this->db->escape($to);
		
		$sql .=	" ORDER BY T.dateapplicable DESC, T.id DESC";

		$rs = $this->db->query($sql)->result();
		
		if (count($rs)) return $rs;
		else return false;
	}
	
	public function markDismissed($userid, $timesheetid) {
		$sql = "INSERT INTO timesheet_dismissed
				SET usersid = ".intval($userid).", 
					timesheetid = ".intval($timesheetid);
	
		return $this->db->query($sql);
	}
	
	public function markNotDismissed($userid, $timesheetid) {
		$sql = "DELETE FROM timesheet_dismissed
				WHERE usersid = ".intval($userid)."
					AND timesheetid = ".intval($timesheetid);
	
		return $this->db->query($sql);
	}
	
}

/*
	ALTER TABLE `timesheet` ADD `public` tinyint NOT NULL DEFAULT '0'  AFTER `activityid`;
*/

?>